#！/bin/bash
source /etc/profile
if [ -n "$1" ]
then
  do_date=$1
else
  do_date=`date -d "-1 day" +%F`
fi
sql="
insert overwrite table lagou_shops_his

-- 当日新增部分
select shopid, shopname, status, createtime, modifytime, '$do_date' as start_date, '9999-12-31' as end_date
from lagou_shops where createtime = '$do_date'

union all

-- 当日变更部分
select a.shopid, a.shopname, a.status, a.createtime, a.modifytime, a.start_date,
case when a.end_date = '9999-12-31' and b.userid is not null
     then date_add('$do_date', -1)
     else a.end_date
end as end_date
'$do_date' as end_date
from lagou_shops_his a
left join (
     select * from lagou_shops where modifytime = '$do_date'
) b on a.shopid = b.shopid
modifytime = '$do_date'
"
hive -e "$sql"